package com.dao;

import com.model.Student;
import com.utils.JdbcUtils;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.HashMap;

public class StudentDao {
    
    public List<Map<String, Object>> getAllStudents() throws SQLException {
        String sql = "SELECT * FROM Student ORDER BY Sno";
        List<Map<String, Object>> students = new ArrayList<>();
        
        try (Connection conn = JdbcUtils.getDs().getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            
            while (rs.next()) {
                Map<String, Object> student = new HashMap<>();
                student.put("sno", rs.getInt("Sno"));
                student.put("name", rs.getString("Sname"));
                student.put("sex", rs.getInt("Ssex"));
                student.put("age", rs.getInt("Sage"));
                student.put("birthday", rs.getDate("Birthday"));
                student.put("department", rs.getString("Sdept"));
                students.add(student);
            }
        }
        return students;
    }
    public List<Student> getAllStudentList() throws SQLException {
        String sql = "SELECT * FROM Student ORDER BY Sno";
        List<Student> students = new ArrayList<>();

        try (Connection conn = JdbcUtils.getDs().getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {

            while (rs.next()) {
                Student student = new Student();
                student.setSno( rs.getInt("Sno"));
                student.setSname(rs.getString("Sname"));
                student.setSsex( rs.getInt("Ssex"));
                student.setSage( rs.getInt("Sage"));
                student.setBirthday(rs.getDate("Birthday"));
                student.setSdept(rs.getString("Sdept"));
                students.add(student);
            }
        }
        return students;
    }
    
    public void addStudent(Map<String, Object> student) throws SQLException {
        String sql = "INSERT INTO Student (Sno, Sname, Ssex, Sage, Birthday, Sdept) VALUES (?, ?, ?, ?, ?, ?)";
        
        try (Connection conn = JdbcUtils.getDs().getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, (Integer) student.get("sno"));
            pstmt.setString(2, (String) student.get("name"));
            pstmt.setInt(3, (Integer) student.get("sex"));
            pstmt.setInt(4, (Integer) student.get("age"));
            pstmt.setDate(5, (Date) student.get("birthday"));
            pstmt.setString(6, (String) student.get("department"));
            
            pstmt.executeUpdate();
        }
    }
    public void addStudentEntity(Student student) throws SQLException {
        String sql = "INSERT INTO Student (Sno, Sname, Ssex, Sage, Birthday, Sdept) VALUES (?, ?, ?, ?, ?, ?)";

        try (Connection conn = JdbcUtils.getDs().getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, student.getSno());
            pstmt.setString(2, student.getSname());
            pstmt.setInt(3, student.getSsex());
            pstmt.setInt(4, student.getSage());
            java.util.Date utilDate =  student.getBirthday();
            pstmt.setDate(5, new java.sql.Date(utilDate.getTime()));
            pstmt.setString(6, student.getSdept());

            pstmt.executeUpdate();
        }
    }
    
    public void updateStudent(Map<String, Object> student) throws SQLException {
        String sql = "UPDATE Student SET Sname = ?, Ssex = ?, Sage = ?, Birthday = ?, Sdept = ? WHERE Sno = ?";
        
        try (Connection conn = JdbcUtils.getDs().getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, (String) student.get("name"));
            pstmt.setInt(2, (Integer) student.get("sex"));
            pstmt.setInt(3, (Integer) student.get("age"));
            pstmt.setDate(4, (Date) student.get("birthday"));
            pstmt.setString(5, (String) student.get("department"));
            pstmt.setInt(6, (Integer) student.get("sno"));
            
            pstmt.executeUpdate();
        }
    }
    public void updateStudent(Student student) throws SQLException {
        String sql = "UPDATE Student SET Sname = ?, Ssex = ?, Sage = ?, Birthday = ?, Sdept = ? WHERE Sno = ?";

        try (Connection conn = JdbcUtils.getDs().getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, student.getSno());
            pstmt.setString(2, student.getSname());
            pstmt.setInt(3, student.getSsex());
            pstmt.setInt(4, student.getSage());
            java.util.Date utilDate =  student.getBirthday();
            pstmt.setDate(5, new java.sql.Date(utilDate.getTime()));
            pstmt.setString(6, student.getSdept());

            pstmt.executeUpdate();
        }
    }
    
    public void deleteStudent(int sno) throws SQLException {
        String sql = "DELETE FROM Student WHERE Sno = ?";
        
        try (Connection conn = JdbcUtils.getDs().getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, sno);
            pstmt.executeUpdate();
        }
    }
} 